package com.qingke.easyjava.jdbc.app;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.codec.digest.DigestUtils;

import com.qingke.easyjava.jdbcapp.pojo.Answer;
import com.qingke.easyjava.jdbcapp.pojo.Player;
import com.qingke.easyjava.jdbcapp.pojo.Question;
import com.qingke.easyjava.jdbcapp.util.PropertyManager;

public class BeSuperDao {
	public BeSuperDao() {

	}

	public boolean isUsernameExists(String username) {
		String sql = "select 'Y' from player where username = ?";
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;

		try {
			conn = getConnection();

			statement = conn.prepareStatement(sql);

			statement.setString(1, username);

			rs = statement.executeQuery();

			if (rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e);
		} finally {
			cleanup(conn, statement, rs);
		}

		return false;

	}

	private void cleanup(Connection conn, PreparedStatement statement, ResultSet rs) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
		if (statement != null) {
			try {
				statement.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	public Connection getConnection() throws SQLException {

		String driver = PropertyManager.getInstance().getProperty("db.driver");
		String url = PropertyManager.getInstance().getProperty("db.url");
		String user = PropertyManager.getInstance().getProperty("db.username");
		String password = PropertyManager.getInstance().getProperty("db.password");

		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		Connection conn = DriverManager.getConnection(url, user, password);
		return conn;
	}

	public Player signup(String username, String password, String nickname) {
		String sql = "insert into besuper.player(name,username,password) values (?,?,?)";
		Connection conn = null;
		PreparedStatement statement = null;
		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setString(1, nickname);
			statement.setString(2, username);
			statement.setString(3, DigestUtils.md5Hex(password));

			int affectRows = statement.executeUpdate();
			if (affectRows > 0) {
				return login(username, password);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			cleanup(conn, statement, null);
		}
		return null;
	}

	public Player login(String username, String password) {
		String sql = "SELECT id, name, score FROM player WHERE username = ? AND password = ?";

		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		Player player = null;

		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setString(1, username);
			statement.setString(2, DigestUtils.md5Hex(password));

			rs = statement.executeQuery();
			while (rs.next()) {
				long id = rs.getLong("id");
				String name = rs.getString("name");
				int score = rs.getInt("score");
				player = new Player(id, name, score);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();

		} finally {
			cleanup(conn, statement, rs);
		}
		return player;

	}

	public List<Question> getQuestions() {
		List<Question> questions = new ArrayList<>();
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;

		String sql = "select id,value,credit,isopen,player_id from question order by isopen";
		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			rs = statement.executeQuery();
			while (rs.next()) {
				long id = rs.getLong("id");
				String value = rs.getString("value");
				int credit = rs.getInt("score");
				boolean isOpen = "Y".equals(rs.getString("isopen"));
				long playerId = rs.getLong("player_id");
				Player player = this.getPlayer(playerId);
				Question question = new Question(id, value, player, credit);
				question.setOpen(isOpen);
				List<Answer> answers = this.getAnswersFrom(question);
				question.setAnswers(answers);
				questions.add(question);
			}

		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			cleanup(conn, statement, rs);
		}
		return questions;
	}

	public Player getPlayer(long playerId) {
		String sql = "SELECT id, name, score FROM player WHERE id = ?";
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		Player player = null;
		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			rs = statement.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				int score = rs.getInt("score");
				player = new Player(id, name, score);
			}
			return player;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			cleanup(conn, statement, rs);
		}
		return null;
	}

	public List<Answer> getAnswersFrom(Question question) {
		String sql = "select id,value,best,player_id from answer where question_id=?";
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet rs = null;
		List<Answer> answers = new ArrayList<>();
		try {
			conn = getConnection();
			statement = conn.prepareStatement(sql);
			statement.setLong(1, question.getId());
			rs = statement.executeQuery();
			while (rs.next()) {
				long id = rs.getLong("id");
				String value = rs.getString("value");
				boolean isBest = "Y".equals(rs.getString("best"));
				long playerId = rs.getLong("player_id");
				Player player = this.getPlayer(playerId);
				Answer answer = new Answer(id, value, player);
				answer.setQuestionId(question.getId());
				answer.setBestAnswer(isBest);

				answers.add(answer);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			cleanup(conn, statement, rs);
		}
		return answers;
	}

	public List<Answer> getAnswersFrom(Player player) {
		String sql ="select id,value,best,question_id from answer where player_id=?";
		Connection conn=null;
		PreparedStatement statement =null;
		ResultSet rs=null;
		List<Answer> answers=new ArrayList<>();
		try {
			conn=getConnection();
			statement=conn.prepareStatement(sql);
			statement.setLong(1, player.getId());
			rs=statement.executeQuery();
			while(rs.next()){
				long id = rs.getLong("id");
				String value = rs.getString("value");
				boolean isBest = "Y".equals(rs.getString("best"));
				long questionId = rs.getLong("question_id");
				Answer answer =new Answer(id, value, player);
				answer.setBestAnswer(isBest);
				answer.setQuestionId(questionId);
				answers.add(answer);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			cleanup(conn, statement, rs);
		}
		return answers;
	}

	public List<Question> getQuestionsFrom(Player player) {
		List<Question> questions =new ArrayList<>();
		Connection conn =null;
		PreparedStatement statement=null;
		ResultSet rs =null;
		String sql="select id,value,credit,isopen from question where player_id=?";
		try {
			conn=getConnection();
			statement=conn.prepareStatement(sql);
			statement.setLong(1, player.getId());
			rs=statement.executeQuery();
			while(rs.next()){
				long id =rs.getLong("id");
				String value =rs.getString("value");
				int credit=rs.getInt("credit");
				boolean isOpen ="Y".equals(rs.getString("isopen"));
				Question question =new Question(id, value, player, credit);
				List<Answer> answers=this.getAnswersFrom(question);
				question.setOpen(isOpen);
				question.setAnswers(answers);
				
				questions.add(question);
				
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			cleanup(conn, statement, rs);
		}
		return questions;
	}

	public Question getQuestion(long questionId) {
		String sql="select * from question where id=? ";
		Connection conn=null;
		PreparedStatement statement =null;
		ResultSet rs=null;
		Question question=null;
		try {
			conn=getConnection();
			statement =conn.prepareStatement(sql);
			statement.setLong(1,questionId);
			rs=statement.executeQuery();
			while(rs.next()){
				long id = rs.getLong("id");
				String value = rs.getString("value");
				int credit = rs.getInt("score");
				boolean isOpen = "Y".equals(rs.getString("isopen"));
				long playerId = rs.getLong("player_id");
				Player player =this.getPlayer(playerId);
				question =new Question(id, value, player, credit);
				List<Answer> answers= this.getAnswersFrom(question);
				question.setAnswers(answers);
				question.setOpen(isOpen);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			cleanup(conn, statement, rs);
		}
		return question;
	}

	public void upsertQuestion(Question question) {
		 Connection conn = null;
	        PreparedStatement statement = null;
	        ResultSet rs = null;

	        String sqlInsert = "INSERT INTO besuper.question (`value`, `credit`, `player_id`, `isOpen`) VALUES (?,?,?,?)";
	        String sqlUpdate = "UPDATE besuper.question SET `value`=?, `credit`=?, `player_id`=?, `isOpen`=? WHERE `id`=?";
	        boolean isUpdate = question.getId() != -1;
	        try {
	            conn = getConnection();
	            
	            String sql = sqlInsert;
	            if (isUpdate) {
	                sql = sqlUpdate;
	            }
	            statement = conn.prepareStatement(sql);
	            
	            statement.setString(1, question.getValue());
	            statement.setInt(2, question.getCredit());
	            statement.setLong(3, question.getFrom().getId());
	            statement.setString(4, question.isOpen() ? "Y" : "N");
	            if (isUpdate) {
	                statement.setLong(5, question.getId());
	            }
	            
	            statement.executeUpdate();

	        } catch (SQLException e) {
	            e.printStackTrace();
	        } finally {
	            cleanup(conn, statement, rs);
	        }
	}

	public void upsertAnswer(Answer answer) {
		 Connection conn = null;
	     PreparedStatement statement = null;
	     ResultSet rs = null;
	     
	     String sqlInsert ="insert into answer(value,best,player_id,question_id) values(?,?,?,?) ";
	     String sqlUpdate="update answer set value=?,best=?,player_id=?,question_id=? where id=?";
	     boolean isUpdate=answer.getId()!=-1;
	     try {
			conn=getConnection();
			String sql=sqlInsert;
			if(isUpdate){
				sql=sqlUpdate;	
			}
			statement=conn.prepareStatement(sql);
			statement.setString(1, answer.getValue());
			statement.setString(2, answer.isBestAnswer()?"Y":"N");
			statement.setLong(3, answer.getFrom().getId());
			statement.setLong(4, answer.getQuestionId());
			if(isUpdate){
				statement.setLong(5,answer.getId());
			}
			
			statement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			cleanup(conn, statement, rs);
		}
		
	}

	public Answer getAnswer(long answerId) {
		Connection conn=null;
		PreparedStatement statement=null;
		ResultSet rs=null;
		Answer answer=null;
		String sql ="select id,value,best,player_id,question_id from answer where id=?";
		try {
			conn=getConnection();
			statement=conn.prepareStatement(sql);
			statement.setLong(1, answerId);
			rs=statement.executeQuery();
			while(rs.next()){
				long id=rs.getLong("id");
				String value =rs.getString("value");
				boolean isBest ="Y".equals(rs.getString("best"));
				long playerId =rs.getLong("player_id");
				long questionId =rs.getLong("question_id");
				Player player =this.getPlayer(playerId);
				answer=new Answer(id, value, player);
				answer.setBestAnswer(isBest);
				answer.setQuestionId(questionId);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			cleanup(conn, statement, rs);
		}
		return answer;
	}

	public void acceptAnswer(Question question, Answer answer) {
		Connection conn=null;
		PreparedStatement statement =null;
		PreparedStatement statement2 =null;
		ResultSet rs=null;
		String sql ="update answer set best='Y' where id=?";
		String sql2 ="update question set isopen='N' where id=?";
		
		try {
			conn=getConnection();
			conn.setAutoCommit(false);
			
			statement=conn.prepareStatement(sql);
			statement.setLong(1, answer.getId());
			statement.execute();
			
			statement2=conn.prepareStatement(sql2);
			statement2.setLong(1, question.getId());
			statement2.execute();
			
			conn.commit();
			conn.setAutoCommit(true);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}finally{
			cleanup(conn, statement, rs);
		}
		
	}
}
